DELETE Removes all rows from a table or specified partitions of a table, without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.
DELETE:
● Removes some or all rows from a table.
● A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed.
● Causes all DELETE triggers on the table to fire.
● It deal-located records row-by-row in transaction logs and thus is slower than TRUNCATE.
● According to MS BOL, if a table is a heap or no clustered index is defined than the row-pages emptied are not deal-located and remain allocated in the heap. Thus no other object can reuse this associated space. Thus to deal-locate the space a Clustered index is required or TABLOCK hint should be applied in the DELETE statement.
● Thus it requires more locks and database resources.
● This is a DML command as its is just used to manipulate/modify the table data. It does not change the property of a table.
TRUNCATE:
● Removes all rows from a table.
● Does not require a WHERE clause, not allowed here.
● Identity columns are re-seeded on this operation, if no seed was defined then the default value 1 is used.
● No triggers are fired on this operation because it does not log individual rows.
● It deal-locates data pages instead of rows in transaction logs, thus is faster than DELETE.
● Thus it also requires less number of locks.
● TRUNCATE is not possible when a table is reference by a Foreign Key or tables used in replication or with Indexed views.
● This is a DDL command as its resets identity columns, deal-locates data pages and empty them for use of other objects in the database.
Note: It is a misconception among some people that TRUNCATE cannot be rolled back. But in reality TRUNCATE operation can be ROLLED BACKED. Thus DELETE and TRUNCATE both can be rollbacked if provided inside a transaction. The only method to rollback a committed transaction after DELETE/TRUNCATE is to restore the last backup and run transactions logs till the time when DELETE/TRUNCATE is about to happen.
DELETE:
● Removes some or all rows from a table.
● A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed.
● Causes all DELETE triggers on the table to fire.
● It deal-located records row-by-row in transaction logs and thus is slower than TRUNCATE.
● According to MS BOL, if a table is a heap or no clustered index is defined than the row-pages emptied are not deal-located and remain allocated in the heap. Thus no other object can reuse this associated space. Thus to deal-locate the space a Clustered index is required or TABLOCK hint should be applied in the DELETE statement.
● Thus it requires more locks and database resources.
● This is a DML command as its is just used to manipulate/modify the table data. It does not change the property of a table.
TRUNCATE:
● Removes all rows from a table.
● Does not require a WHERE clause, not allowed here.
● Identity columns are re-seeded on this operation, if no seed was defined then the default value 1 is used.
● No triggers are fired on this operation because it does not log individual rows.
● It deal-locates data pages instead of rows in transaction logs, thus is faster than DELETE.
● Thus it also requires less number of locks.
● TRUNCATE is not possible when a table is reference by a Foreign Key or tables used in replication or with Indexed views.
● This is a DDL command as its resets identity columns, deal-locates data pages and empty them for use of other objects in the database.
Note: It is a misconception among some people that TRUNCATE cannot be rolled back. But in reality TRUNCATE operation can be ROLLED BACKED. Thus DELETE and TRUNCATE both can be rollbacked if provided inside a transaction. The only method to rollback a committed transaction after DELETE/TRUNCATE is to restore the last backup and run transactions logs till the time when DELETE/TRUNCATE is about to happen.
سید حامد واحدی
27 تير 1394